﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using ARMS.BaseClasses;

namespace ARMS.Models
{
    public class PartOrderModel : ARMS.BaseClasses.BaseModel
    {
        string dbConn = "Data Source=(LocalDB)\\v11.0;;AttachDbFilename=|DataDirectory|\\arms.mdf;Integrated Security=True;Database=ARMS";

        /* FIELDS
            [PartOrderID] INT IDENTITY (1, 1) NOT NULL,
            [WorkOrderID] INT NOT NULL,
            [PartID]      INT NOT NULL,
            [Quantity]
         */

        #region Create Part Order

        public int createPartOrder(int PartID, int Quantity, int WorkOrderID)
        {
            //Connect to Database
            SqlConnection connection = new SqlConnection(dbConn);
            connection.Open();
            int affectedRows = 0;

            try
            {
                //create new part order
                string sqlQuery = "INSERT INTO PartOrder (PartID, Quantity, WorkOrderID) VALUES(" + PartID + ", " + Quantity + ", " + WorkOrderID + ")";
                SqlCommand command = new SqlCommand(sqlQuery, connection);
                affectedRows = command.ExecuteNonQuery();
            }
            catch
            {
                throw new Exception();
            }

            connection.Close();
            return affectedRows;
        }

        #endregion

        #region Update Part Order

        public int updatePartOrder(int Quantity, int PartOrderID)
        {
            int affectedRows = 0;

            //Connect to Database
            SqlConnection connection = new SqlConnection(dbConn);
            connection.Open();

            try
            {
                //create sql for update work order
                string sqlQuery = "UPDATE PartOrder SET Quantity=" + Quantity + " WHERE ((PartOrderID=" + PartOrderID + "))";
                SqlCommand command = new SqlCommand(sqlQuery, connection);
                affectedRows = command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                throw new Exception();
            }

            connection.Close();
            return affectedRows;
        }

        #endregion

        #region Delete Part Order

        public int deletePartOrder(int PartOrderID)
        {
            int affectedRows = 0;

            //Connect to Database
            SqlConnection connection = new SqlConnection(dbConn);
            connection.Open();

            try
            {
                //create sql for delete work order
                string sqlQuery = "DELETE FROM PartOrder WHERE ((PartOrderID= " + PartOrderID + "))";
                SqlCommand command = new SqlCommand(sqlQuery, connection);
                affectedRows = command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                throw new Exception();
            }

            connection.Close();
            return affectedRows;
        }

        #endregion

        #region List Part Order associated with Work Order ID

        public DataTable listPartOrderByWorkOrderID(int WorkOrderID)
        {
            DataTable results = new DataTable();
            //Connect to Database
            SqlConnection connection = new SqlConnection(dbConn);
            connection.Open();

            try
            {
                //connect to database
                string sqlQuery = "Select * FROM PartOrder WHERE ((WorkOrderID= " + WorkOrderID + "))";
                SqlCommand cmd = new SqlCommand(sqlQuery, connection);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);

                adapter.Fill(results);
                adapter.Dispose();
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                throw new Exception();
            }

            connection.Close();
            return results;
        }

        #endregion

        #region List Part Order

        public DataTable listPartOrder()
        {
            DataTable results = new DataTable();
            //Connect to Database
            SqlConnection connection = new SqlConnection(dbConn);
            connection.Open();

            try
            {
                //connect to database
                string sqlQuery = "Select * FROM PartOrder";
                SqlCommand cmd = new SqlCommand(sqlQuery, connection);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);

                adapter.Fill(results);
                adapter.Dispose();
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                throw new Exception();
            }

            connection.Close();
            return results;
        }

        #endregion
    }
}